from pymysql import Connect
from pymysql.cursors import DictCursor

# 默认port=6379, db=0
from redis import Redis

_rds = Redis('10.36.174.38', decode_responses=True)

class DBConn():
    def __init__(self):
        # cursorclass指定的DictCursor，其作用将下载的结果封装成 [{}, ...]或 {}
        self.conn = Connect(host='10.36.174.38',
                            port=3306,
                            user='root',
                            password='root',
                            db='apidb',
                            charset='utf8',
                            cursorclass=DictCursor)

    def __enter__(self):
        return self.conn.cursor()

    def __exit__(self, exc_type, exc_val, exc_tb):
        if exc_type:
            print('--->数据库操作异常', exc_val)
            self.conn.rollback()
        else:
            self.conn.commit()
        return True

    def close(self):
        try:
            self.conn.close()
        except:
            pass

# print('--Connected Ok-')
class Dao():
    def __init__(self):
        self.db = DBConn()

    def __del__(self):
        self.db.close()

    def save(self, table, pk='id', **item) -> int:
        sql = 'insert into %s(%s) values(%s)'
        fields = ','.join(item)
        values = ','.join(['%%(%s)s' % k for k in item])
        with self.db as c:
            if c.execute(sql % (table, fields, values), args=item):
                sql = f'select max({pk}) as next_id from {table}'
                c.execute(sql)
                ret = c.fetchone()  # Cursor类查询结果 (1, )， DictCursor查询结果 {'next_id': 10}
                return ret['next_id']  # 抛出KeyError异常，因为ret是dict字典对象
        return 0


    def update(self, table, pk='id', extra_where_field=None, **item):
        sql = 'update %s set %s where %s'
        # 从item中获取主键的字段值
        # item => {'name': 'disen', 'sex': '男', 'id': 1}
        # update_fields => name=%(name)s , sex=%(sex)s
        update_fields = ','.join(['%s=%%(%s)s' % (k, k) for k in item if k != pk])
        where = f'{pk}=%({pk})s'
        where = where + (f' and {extra_where_field}=%({extra_where_field})s' if extra_where_field else '')

        with self.db as c:
            c.execute(sql % (table, update_fields, where), args=item)
            return c.rowcount > 0


    def query(self, table, *field, where=None, args=None, one=False):
        sql = 'select %s from %s' % (','.join(field), table)
        if where:
            sql += " where " + where
        with self.db as c:
            c.execute(sql, args=args)
            ret = c.fetchone() if one else c.fetchall()

        return ret

    def delete(self, table, where, args=None):
        with self.db as c:
            # execute()执行insert, update, delete等语句时，返回是影响的行数
            return c.execute('delete from %s where %s' %(table, where), args=args)

        return 0